﻿/****** Object:  Database [SyncCache]    Script Date: 2023/4/3 10:42:03 ******/
CREATE DATABASE [SyncCache]
GO

USE [SyncCache]
GO

CREATE TABLE [dbo].[AssetEvent](
	[EventId] [nvarchar](200) NOT NULL,
	[EventType] [int] NOT NULL,
	[EventData] [nvarchar](max) NULL,
	[RequestTime] [datetime] NULL,
	[ProcessStatus] [int] NULL,
	[ProcessTime] [datetime] NULL,
	[Barcode] [nvarchar](255) NULL,
	[CallbackData] [nvarchar](max) NULL,
 CONSTRAINT [PK_AssetEvent] PRIMARY KEY CLUSTERED 
(
	[EventId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Index [IX_AssetEvent]    Script Date: 2023/4/3 10:42:03 ******/
CREATE NONCLUSTERED INDEX [IX_AssetEvent] ON [dbo].[AssetEvent]
(
	[RequestTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[SelectAssetEvent]    Script Date: 2023/4/3 10:42:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[SelectAssetEvent](
	@EventType INT,
	@Start DATETIME,
	@End DATETIME,
	@ProcessStatus INT = 0,
	@OnlyLastChange BIT = 0,
	@ToNextStep BIT = 1)
AS 
BEGIN
	IF (@OnlyLastChange = 1)
	BEGIN
		SELECT TOP 1000 * INTO #T1 FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY RequestTime DESC) AS RowId FROM AssetEvent WHERE RequestTime BETWEEN @Start AND @End AND (@EventType = 0 OR EventType=@EventType) AND ProcessStatus = @ProcessStatus) AS T WHERE T.RowId=1
		IF (@ToNextStep = 1) 
		BEGIN
			UPDATE dbo.AssetEvent SET ProcessStatus = @ProcessStatus + 2 WHERE Barcode IN (SELECT Barcode FROM #T1) AND RequestTime BETWEEN @Start AND @End AND (@EventType = 0 OR EventType=@EventType) AND ProcessStatus = @ProcessStatus;
			UPDATE dbo.AssetEvent SET ProcessStatus = @ProcessStatus + 1 WHERE EventId IN (SELECT EventId FROM #T1);
		END

		SELECT * FROM #T1;
		DROP TABLE #T1;
	END
	ELSE
	BEGIN
		SELECT TOP 1000 * INTO #T2 FROM dbo.AssetEvent WHERE RequestTime BETWEEN @Start AND @End AND (@EventType = 0 OR EventType=@EventType) AND ProcessStatus = @ProcessStatus  ORDER BY RequestTime ASC;
		IF (@ToNextStep = 1) 
			UPDATE dbo.AssetEvent SET ProcessStatus = @ProcessStatus + 1 WHERE EventId IN (SELECT EventId FROM #T2);

		SELECT * FROM #T2;
		DROP TABLE #T2;
	END
END
GO
ALTER DATABASE [SyncCache] SET READ_WRITE 
GO
